package com.gps.pe.mock.examples;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.log4j.Logger;

import com.gps.pe.dao.DatabaseConnection;

/**
 * This is a mock up test class to test database connectivity
 * 
 * URL to access this - http://localhost:8080/placementengine/testDatabaseConnection
 * 
 * @author GPS
 *
 */
@SuppressWarnings("serial")
@WebServlet(name = "TestDatabaseConnection", urlPatterns = { "/testDatabaseConnection" })
public class TestDatabaseConnection extends HttpServlet {
	private static final Logger LOGGER = Logger.getLogger(TestDatabaseConnection.class);

	public void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
		ResultSet resultSet = null;
		LOGGER.info("Testing Database connection...");
		try (Connection connection = DatabaseConnection.getInstance().getConnection();) {
			// Get Connection and Statement
			String sql = "select * from Role where RoleId >= ?";
			LOGGER.debug("SQL Query for testing is [" + sql + "]");
			PreparedStatement pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, 1);

			resultSet = pstmt.executeQuery();
			LOGGER.debug("Executing SQL Query...");
			while (resultSet.next()) {
				LOGGER.debug(resultSet.getInt("roleId") + "  " + resultSet.getString("roleName") + "  "
						+ resultSet.getString("readAccess") + "  " + resultSet.getString("writeAccess"));
			}
			LOGGER.info("Database connection successfully established...");
		} catch (SQLException e) {
			LOGGER.error("Problem to get Database connection, Check DatabaseConnection & context.xml...");
		}
	}
}